package fulbitofacil.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import javax.servlet.http.HttpSession;

import fulbitofacil.exception.DAOExcepcion;
import fulbitofacil.modelo.CanchaFul;
import fulbitofacil.modelo.Cliente;
import fulbitofacil.modelo.Comentario;
import fulbitofacil.modelo.HorarioAtencion;
import fulbitofacil.util.ConexionBD;

public class CanchaFulDAO extends BaseDAO {

	public CanchaFul insertar(CanchaFul ca) throws DAOExcepcion {
		System.out.println("CanchaFulDAO: insertar()");

		Connection con = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;

		String query = "INSERT INTO canchaful (nombre, caracteristicas, foto, Local_idLocal) VALUES (?,?,?,?)";
		// String query2 =
		// "INSERT INTO diasatencion (fecha, CanchaFul_idCanchaFul) VALUES (?,?)";
		// String query3 =
		// "INSERT INTO horaatencion (hora, tarifa, estado, DiasAtencion_idDiasAtencion) VALUES (?,?,?,?)";

		try {
			con = ConexionBD.obtenerConexion();
			// con.setAutoCommit(false);

			stmt = con.prepareStatement(query);
			stmt.setString(1, ca.getNombre());
			stmt.setString(2, ca.getCaractetisticas());
			stmt.setString(3, ca.getFoto());
			
			stmt.setInt(4, ca.getMyLocal().getIdLocal());
			

			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}

			int idp = 0;
			query = "SELECT LAST_INSERT_ID()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				idp = rs.getInt(1);
			}
			ca.setIdCanchaFul(idp);

			int mod = 0, dia = 0;
			int idDia = 0;
			for (HorarioAtencion hor : ca.getMyHorarioAtencion()) {
				System.out.println("dia " + dia);
				if (mod % 24 == 0) {
					query = "INSERT INTO diasatencion (fecha, CanchaFul_idCanchaFul) VALUES (?,?)";
					stmt = con.prepareStatement(query);
					stmt.setDate(1, (Date) hor.getFecha());
					stmt.setInt(2, idp);
					dia = stmt.executeUpdate();
					if (dia != 1) {
						throw new SQLException("No se pudo insertar");
					}

					query = "SELECT LAST_INSERT_ID()";
					stmt = con.prepareStatement(query);
					rs = stmt.executeQuery();
					if (rs.next()) {
						idDia = rs.getInt(1);
					}
				}
				
					query = "INSERT INTO horaatencion (hora, tarifa, estado, DiasAtencion_idDiasAtencion) VALUES (?,?,?,?)";
					stmt = con.prepareStatement(query);

					stmt.setInt(1, hor.getHora());
					System.out.println("i " + i + "hora:" + hor.getHora());
					stmt.setDouble(2, hor.getTarifa());
					System.out.println("i " + i + "tarifa:" + hor.getTarifa());
					stmt.setInt(3, hor.getEstado());
					System.out.println("i " + i + "estado:" + hor.getEstado());
					stmt.setInt(4, idDia);
					System.out.println("i " + i + "dia:" + idDia);

					int u = stmt.executeUpdate();
					if (u != 1) {
						throw new SQLException("No se pudo insertar");
					}
				
				mod++;
			}
			// confirmar la transaccion
			// con.commit();

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return ca;
	}

	public Collection<CanchaFul> listarCanchaPorLocal(int idLocal)
			throws DAOExcepcion {
		Collection<CanchaFul> listaCanchas = new ArrayList<CanchaFul>();
		String query = "SELECT * from CanchaFul where Local_idLocal=" + idLocal;
		Connection con = null;
		PreparedStatement stmt = null;

		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				CanchaFul C = new CanchaFul();

				C.setIdLocal(rs.getInt("Local_idLocal"));
				C.setNombre(rs.getString("nombre"));
				C.setCaractetisticas(rs.getString("caracteristicas"));
				C.setFoto(rs.getString("foto"));
				C.setIdCanchaFul(rs.getInt("idCanchaFul"));

				listaCanchas.add(C);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}

		return listaCanchas;
	}
	
	public Collection<CanchaFul> listarCanchas()
			throws DAOExcepcion {
		Collection<CanchaFul> listaCanchas = new ArrayList<CanchaFul>();
		CanchaFul C;
		String query = "SELECT * from canchaful";
		Connection con = null;
		PreparedStatement stmt = null;

		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				C = new CanchaFul();		
				C.setIdCanchaFul(rs.getInt("idCanchaFul"));
				C.setIdLocal(rs.getInt("Local_idLocal"));
				C.setNombre(rs.getString("nombre"));
				C.setCaractetisticas(rs.getString("caracteristicas"));
				C.setFoto(rs.getString("foto"));
				listaCanchas.add(C);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}

		return listaCanchas;
	}
	
	
	public CanchaFul obtenerCanchaId(int idCancha)
			throws DAOExcepcion {
		CanchaFul C= null;
		String query = "SELECT * from CanchaFul where idCanchaFul=" + idCancha;
		Connection con = null;
		PreparedStatement stmt = null;

		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				C = new CanchaFul();
				C.setIdCanchaFul(rs.getInt("idCanchaFul"));
				C.setIdLocal(rs.getInt("Local_idLocal"));
				C.setNombre(rs.getString("nombre"));
				C.setCaractetisticas(rs.getString("caracteristicas"));
				C.setFoto(rs.getString("foto"));

				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}

		return C;
	}

}
